#!/bin/bash


LOG_DIR="/home/cys/www/script/logs"
# 日志文件名包含日期，每次执行的日志前添加时间戳
FILE_NAME="manual_insert_device_active_one_hour"
LOG_FILE="${LOG_DIR}/${FILE_NAME}_$(date +%Y-%m-%d).log"
mkdir -p "${LOG_DIR}"

# 记录开始时间
echo "[$(date +%Y-%m-%d_%H:%M:%S)] 开始执行 clickhouse 命令" >> "${LOG_FILE}"

clickhouse-client --password Keepgo123@cys -q "
insert into db_cfx.t_data_user_device_active (channel, dev_brand, dev_model, os_version,os_incremental,sdk_version,oaid, vn, vc, gaid, maid, activate_time, activate_ip, status,lockscreen_status,carousel_status)
WITH
    -- 新来的设备
    incoming AS (
       select * from (
      SELECT argMin(channel,base_create_time) as channel,
        argMin(brand,base_create_time) as dev_brand ,
        argMin(model,base_create_time) as dev_model,
        argMin(osv,base_create_time) as os_version,
        argMin(os_incremental,base_create_time) as os_incremental,
        argMin(sdk_version,base_create_time) as sdk_version,
        uuid,
        argMin(vn,base_create_time) as vn,
        argMin(vc,base_create_time) as vc,
        argMin(gaid,base_create_time) as gaid,
        argMin(maid,base_create_time) as maid,
        min(base_create_time) as activate_time,
        argMin(base_client_ip,base_create_time) as activate_ip,
        argMin(base_status ,base_create_time) as status,
        argMin(lockscreen_setting_state, base_create_time) AS lockscreen_setting_state,
        argMin(lockscreen_setting_carousel_state, base_create_time) AS lockscreen_setting_carousel_state
      from db_cfx.t_log_base_data_standard_passive_active
      where (stored_time >= (now() - toIntervalHour(1))) and base_app_id in ('AnySelectedID','AnySelectedNew')
      and base_create_date=today()
      group by uuid
      ) where lockscreen_setting_state !='false'
    )
SELECT i.channel, i.dev_brand, i.dev_model, i.os_version,i.os_incremental,i.sdk_version,i.uuid as oaid, i.vn, i.vc, i.gaid, i.maid, i.activate_time, i.activate_ip, i.status,
CASE WHEN i.lockscreen_setting_state='false' THEN 0 ELSE 1 END as lockscreen_status,CASE WHEN i.lockscreen_setting_carousel_state='false' THEN 0 ELSE 1 END as carousel_status
FROM incoming AS i
ANTI LEFT JOIN (SELECT DISTINCT oaid from db_cfx.t_data_user_device_active where activate_date=today()) AS f
ON i.uuid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_device_new_deduction where channel_name='lockscreen_false' ) AS d
ON i.uuid = d.oaid;"  2>> "${LOG_FILE}"


# 记录结束时间
echo "[$(date +%Y-%m-%d_%H:%M:%S)] shell 脚本执行结束" >> "${LOG_FILE}"
echo "日志已写入 ${LOG_FILE}"